package esi.t2no1.dao.meterdata;

import static esi.t2no1.util.DaoUtil.extractFirst;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

/**
 * 
 * @author Reimo Rebane, Taavi Pauskar
 * 
 */
public class MeterDataDAOImpl implements MeterDataDAO {

    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcInsert createMeterData;

    private static RowMapper<MeterDataDTO> meterDataMapper = new RowMapper<MeterDataDTO>() {

        @Override
        public MeterDataDTO mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            MeterDataDTO md = new MeterDataDTO();
            md.setId(rs.getLong("id"));
            md.setAreaId(rs.getString("areaId"));
            md.setFacilityId(rs.getString("facilityId"));
            md.setReadValue(rs.getString("readValue"));
            md.setReadTime(rs.getDate("readTime"));
            return md;
        }
    };

    @Override
    public Long createMeterData(MeterDataDTO meterData) {
        return createMeterData.executeAndReturnKey(
                new BeanPropertySqlParameterSource(meterData)).longValue();
    }

    @Override
    public List<MeterDataDTO> getMeterData(String supplierId, Date startDate,
            Date endDate) {
        String sql = "select * from meterdata where (areaId in (select areaId from contract where newSupplierId = ?) or facilityId in (select facilityId from contract where newSupplierId = ?)) and readTime >= ? and readTime <= ? order by readTime";
        return simpleJdbcTemplate.query(sql, meterDataMapper, supplierId,
                supplierId, startDate, endDate);
    }

    @Override
    public List<MeterDataDTO> getAllMeterData() {
        String sql = "select * from meterdata";
        return simpleJdbcTemplate.query(sql, meterDataMapper);
    }

    @Override
    public MeterDataDTO getMeterDataById(Long id) {
        String sql = "select * from meterdata where id = ? limit 1";
        return extractFirst(simpleJdbcTemplate.query(sql, meterDataMapper, id));
    }

    @Override
    public MeterDataDTO getLatestMeterData() {
        String sql = "select * from meterdata order by readTime desc limit 0, 1";
        return extractFirst(simpleJdbcTemplate.query(sql,
                meterDataMapper));
    }

    @Override
    public MeterDataDTO getLatestMeterData(String supplierId) {
        String sql = "select * from meterdata where areaId in (select areaId from contract where newSupplierId = ?) or facilityId in (select facilityId from contract where newSupplierId = ?) order by readTime desc, id desc limit 0, 1";
        return extractFirst(simpleJdbcTemplate.query(sql,
                meterDataMapper, supplierId, supplierId));
    }
    
    @Override
    public MeterDataDTO getLatestMeterData(String areaId, String facilityId) {
    	String sql = "select * from meterdata where areaId = ? and facilityId = ? order by readTime desc, id desc limit 0, 1";
        return extractFirst(simpleJdbcTemplate.query(sql,
                meterDataMapper, areaId, facilityId));
    }

    @Autowired
    public void setDataSource(DataSource dataSource) {
        simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        createMeterData = new SimpleJdbcInsert(dataSource).withTableName(
                "meterdata").usingGeneratedKeyColumns("id");
    }
}
